package Back_end;

import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;

public class SQL {
    BookManager Manager = new BookManager();
        public static void Reading(BookManager manager){
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                    conn = Connect.getConnection();
                    stmt = conn.createStatement();
                    rs = stmt.executeQuery("SELECT * FROM Book");

                while (rs.next()) {
                    String loginID = rs.getString("LoginID");
                    String name = rs.getString("Name");
                    String author = rs.getString("Author");
                    String classCode = rs.getString("ClassCode");
                    String publicationUnit = rs.getString("PublicationUnit");
                    String publicationTime = rs.getString("PublicationTime");
                    int price = rs.getInt("Price");

                    manager.SQLAdd(new Book(loginID,name,author,classCode,publicationUnit,publicationTime,price));
                }
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }

        }
        public static void CreatTable() {
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                    conn  =Connect.getConnection();
                    stmt = conn.createStatement();
                    rs = stmt.executeQuery("SELECT * FROM Book");
                    // If the above line throws an exception, it means the table does not exist
                }catch (SQLException e) {
                try {
                    createTable(conn);
                }catch (SQLException e2){
                    e2.printStackTrace();
                }
                }
        }
    private static void createTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "CREATE TABLE Book (" +
                    "LoginID VARCHAR(255) PRIMARY KEY,"+
                    "Name VARCHAR(255), " +
                    "Author VARCHAR(255), " +
                    "ClassCode VARCHAR(255), " +
                    "PublicationUnit VARCHAR(255), " +
                    "PublicationTime VARCHAR(255), " +
                    "Price INT" +
                    ")";
            stmt.executeUpdate(sql);
        }
    }
        public static void Delete(String LoginID){
            Connection conn  =null;
            Statement stmt = null;
            ResultSet rs = null;
            try{
                conn = Connect.getConnection();
                stmt = conn.createStatement();
                String sql = "DELETE FROM Book WHERE LoginID = '"+LoginID+"';";
                stmt.execute(sql);
            }catch (SQLException e){
                e.printStackTrace();
            }finally {
                try{
                    conn.close();
                    stmt.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        public static void Add(Book book){
            Connection conn  =null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            try{
                conn = Connect.getConnection();
                String sql ="INSERT INTO Book (LoginID, Name, Author, ClassCode, PublicationUnit, PublicationTime, Price) VALUES (?, ?, ?, ?, ?, ?, ?)";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, book.getLoginID());
                stmt.setString(2,book.getName());
                stmt.setString(3, book.getAuthor());
                stmt.setString(4, book.getClassCode());
                stmt.setString(5, book.getPublicationUnit());
                stmt.setString(6, book.getPublicationTime());
                stmt.setInt(7, book.getPrice());
                stmt.executeUpdate();
                System.out.println("Book added successfully");
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        public static void update(Book b){
            Connection conn  = null;
            Statement stmt  =null;
            ResultSet rs = null;
            try{
                conn = Connect.getConnection();
                stmt = conn.createStatement();

                String sql = "UPDATE Book SET Name="+b.getName()+"Author="+b.getAuthor()+"ClassCode="+b.getClassCode()
                        +"PublicationUnit="+b.getPublicationUnit()+",PublicationTime="+b.getPublicationTime()+",Price="
                        +b.getPrice()+" WHERE "+b.getLoginID()+";";
                stmt.execute(sql);
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
}
